
package hk.ces.oxfam.patch.transaction;

import hk.ces.oxfam.patch.TransactionPatch;
import hk.ces.oxfam.patch.TransactionStrategy;
import hk.ces.oxfam.patch.TransactionStrategyType;
import hk.ces.oxfam.patch.sql.ExcuteUtil;
import hk.ces.oxfam.patch.db.SQLUtil;
import hk.ces.oxfam.patch.db.result.BeanResult;
import hk.ces.oxfam.patch.db.result.SQLResult;
import hk.ces.oxfam.patch.obj.BatchItem;
import hk.ces.oxfam.patch.obj.BeanPacker;
import hk.ces.oxfam.patch.sql.PreparedSQL;
import hk.ces.oxfam.patch.xml.SQLResultXML;
import hk.ces.oxfam.patch.xml.factory.SQLResultXMLFactory;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.Date;
import java.util.List;
import org.apache.log4j.Logger;

/**
 *
 * @author ReutyCheng
 */
@TransactionStrategy(TransactionStrategyType.ROLLBACK)
public class UnverifiedPaymentPatch implements TransactionPatch
{
  private static final String ONLINE_PAYMENT = "ONLINE_PAYMENT";
  private static final String PAYMENT_OF_DONATION = "PAYMENT_OF_DONATION";
  
  public void run(Connection connection) throws SQLException
  {
    List<BatchItem> batchItems = this.getAllOnlineDonationWithoutAssignment(connection);
    Logger.getLogger(this.getClass()).debug("Total Batch Item [count=" + batchItems.size() + "]");
    
    List<Object[]> relatedPayment = this.getAllRelatedPayment(connection);
    Logger.getLogger(this.getClass()).debug("Total Related Payment [count=" + relatedPayment.size() + "]");

    int resultCount = this.updateAllRelatedPayment(connection);
    Logger.getLogger(this.getClass()).debug("Updated Related Payment [count=" + resultCount + "]");
  }

  private int updateAllRelatedPayment(Connection connection) throws SQLException
  {
    String sqlOne = "select distinct batch_id from batch_paymentinfo where paymentinfos_id in (select id from paymentinfo where disabled = ? and paymentMethod = ? and paymenttype = ? and verified = ?)";
    String sqlTwo = "select temp.batch_id from (select batch_id, count(paymentinfos_id) as paymentCount from batch_paymentinfo group by batch_id) as temp where temp.paymentCount = 1";
    String onlineBatchIdSql = "select id from batch where disabled = ? and id in ((" + sqlOne + ") intersect (" + sqlTwo + "))";
    String batchIdsWithAssignment = "select distinct batch_id from batch_otwteamtotalamount";
    String batchIdsWithoutAssignment = "select id from batch where disabled = ? and id in ((" + onlineBatchIdSql + ") except (" + batchIdsWithAssignment + "))";
    String updateRelatedPaymentSql = "update paymentinfo set verified = ?, lastverifieddate = ?, lastverifiedby = ? where id in (select paymentinfos_id from batch_paymentinfo where batch_id in (" + batchIdsWithoutAssignment + "))";
    Object[] params = new Object[]
    {
      true,
      new Timestamp(new Date().getTime()),
      "LUCKY_DRAW_DBPATCH",
      false,
      false,
      false,
      ONLINE_PAYMENT,
      PAYMENT_OF_DONATION,
      false
    };

    int resultCount = ExcuteUtil.excute(connection, new PreparedSQL(updateRelatedPaymentSql, params));
    return resultCount;
  }

  private List<Object[]> getAllRelatedPayment(Connection connection) throws SQLException
  {
    String sqlOne = "select distinct batch_id from batch_paymentinfo where paymentinfos_id in (select id from paymentinfo where disabled = ? and paymentMethod = ? and paymenttype = ? and verified = ?)";
    String sqlTwo = "select temp.batch_id from (select batch_id, count(paymentinfos_id) as paymentCount from batch_paymentinfo group by batch_id) as temp where temp.paymentCount = 1";
    String onlineBatchIdSql = "select id from batch where disabled = ? and id in ((" + sqlOne + ") intersect (" + sqlTwo + "))";
    String batchIdsWithAssignment = "select distinct batch_id from batch_otwteamtotalamount";
    String batchIdsWithoutAssignment = "select id from batch where disabled = ? and id in ((" + onlineBatchIdSql + ") except (" + batchIdsWithAssignment + "))";
    String unverifiedPaymentIdSql = "select id, invoiceno from paymentinfo where id in (select paymentinfos_id from batch_paymentinfo where batch_id in (" + batchIdsWithoutAssignment + "))";
    Object[] params = new Object[]
    {
      false,
      false,
      false,
      ONLINE_PAYMENT,
      PAYMENT_OF_DONATION,
      false
    };

    SQLResult result = SQLUtil.query(connection, new hk.ces.oxfam.patch.db.sql.PreparedSQL(unverifiedPaymentIdSql, params));
    SQLResultXMLFactory factory = new SQLResultXMLFactory("UnverifiedPaymentInOnlineBatch");
    factory.setXMLItems(new SQLResultXML(result));
    factory.writeToFile();

    return result.getRows();
  }

  private List<BatchItem> getAllOnlineDonationWithoutAssignment(Connection connection) throws SQLException
  {
    String sqlOne = "select distinct batch_id from batch_paymentinfo where paymentinfos_id in (select id from paymentinfo where disabled = ? and paymentMethod = ? and paymenttype = ? and verified = ?)";
    String sqlTwo = "select temp.batch_id from (select batch_id, count(paymentinfos_id) as paymentCount from batch_paymentinfo group by batch_id) as temp where temp.paymentCount = 1";
    String onlineBatchIdSql = "select id from batch where disabled = ? and id in ((" + sqlOne + ") intersect (" + sqlTwo + "))";
    String batchIdsWithAssignment = "select distinct batch_id from batch_otwteamtotalamount";
    String batchIdsWithoutAssignment = "select id, batchno from batch where disabled = ? and id in ((" + onlineBatchIdSql + ") except (" + batchIdsWithAssignment + "))";
    Object[] params = new Object[]
    {
      false,
      false,
      false,
      ONLINE_PAYMENT,
      PAYMENT_OF_DONATION,
      false
    };

    BeanResult<BatchItem> beanResult = new BeanResult<BatchItem>(SQLUtil.query(connection, new hk.ces.oxfam.patch.db.sql.PreparedSQL(batchIdsWithoutAssignment, params)))
    {
      @Override
      public BeanPacker<BatchItem> packer(final SQLResult result)
      {
        return new BeanPacker<BatchItem>()
        {
          public BatchItem pack(Object[] objects)
          {
            BatchItem item = new BatchItem();
            item.setId((Long) objects[0]);
            item.setBatchNo((String) objects[1]);

            return item;
          }
        };
      }
    };

    SQLResultXMLFactory factory = new SQLResultXMLFactory("OnlineBatchWithUnverifiedPaymentWithoutAssignment");
    factory.setXMLItems(new SQLResultXML(beanResult));
    factory.writeToFile();

    return beanResult.getPackedResult();
  }
}
